﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MISAPP.Common;
using System.Data;
using System.Transactions;

namespace MISAPP.Classify.DataAccess
{
    public class DA_CreditCardCount:DA_Matrix
    {
        protected bool setTableSchema = false;

        public DA_CreditCardCount()
        {
            tableName = "credit_card_count";
        }

        public void Check(string p_ymd)
        {
            string sql = "DELETE FROM credit_card_count WHERE ymd = @p_ymd";
            System.Data.Common.DbCommand cmd = DAL.GetCommand();

            cmd.Parameters.Add(DAL.GetParameter("@p_ymd", System.Data.DbType.Date, p_ymd
                  , cmd.CreateParameter()));
            cmd.CommandText = sql;
            DAL.ExecuteNoneQuery(cmd);
        }

        public bool Insert(object[,] p_cardTypes,object[,] p_circulates
            ,object[,] p_effectives,string p_ymd)
        {
            string sql = @"
INSERT INTO credit_card_count(card_type,circulate,effective,ymd) 
VALUES(@p_cardType,@p_circulate,@p_effective,@p_ymd)";

            if (setTableSchema == false)
            {
                setTableSchema = true;
                FillTableSchema(tableName);
            }
            int iResult = -1;
            using (TransactionScope scop = new TransactionScope())
            {
                try
                {
                    for (int i = 1; i <= p_cardTypes.Length; i++)
                    {
                        if (p_cardTypes[1, i] != null)
                        {
                            ResetCriteria();
                            FillFieldList(new string[] { p_cardTypes[1,i].ToString().Replace('\n',' ') }
                                , new string[] { "@p_cardType" }, "card_type");
                            FillFieldList(new string[] { p_circulates[1,i].ToString() }
                                , new string[] { "@p_circulate" }, "circulate");
                            FillFieldList(new string[] { p_effectives[1,i].ToString() }
                                , new string[] { "@p_effective" }, "effective");
                            FillFieldList(new string[] { p_ymd }, 
                                new string[] { "@p_ymd" }, "ymd");
                            AddParameter(sql);
                            iResult = Common.DAL.ExecuteNoneQuery(cmd);
                            if (iResult < 0)
                            {
                                throw new Exception("Insert Fail");
                            }
                        }
                    }
                    scop.Complete();
                }
                catch (Exception ex)
                {
                    iResult = -1;
                }
            }

            return iResult < 0 ? false : true;
        }

        public DataSet GetSummaryData(string[] p_shtName)
        {
            DataSet ds = new DataSet();
            DataTable dt = getData(getCirculateSQLStatement()
                , p_shtName[0].ToString());

            ds.Tables.Add(dt);
            dt = getData(getEffectiveSQLStatement()
                , p_shtName[1].ToString());
            ds.Tables.Add(dt);
            dt = getData(getCirculatePercentSQLStatement()
                , p_shtName[2].ToString());
            ds.Tables.Add(dt);
            dt = getData(getThisLastSQLStatement()
                , p_shtName[3].ToString());
            ds.Tables.Add(dt);
            dt = getData(getLastYearSQLStatement()
                , p_shtName[4].ToString());
            ds.Tables.Add(dt);
            return ds;
        }


        /// <summary>
        /// 流通卡
        /// </summary>
        /// <returns></returns>
        protected string getCirculateSQLStatement()
        {
            return string.Format(@"
SELECT 
A.card_type
,ISNULL([1月] ,0)[1月]
,ISNULL([2月] ,0)[2月]
,ISNULL([3月] ,0)[3月]
,ISNULL([4月] ,0)[4月]
,ISNULL([5月] ,0)[5月]
,ISNULL([6月] ,0)[6月]
,ISNULL([7月] ,0)[7月]
,ISNULL([8月] ,0)[8月]
,ISNULL([9月] ,0)[9月]
,ISNULL([10月] ,0)[10月]
,ISNULL([11月] ,0)[11月]
,ISNULL([12月] ,0)[12月]
FROM 
(
{0}
)A LEFT OUTER JOIN (
					SELECT 
                        {1}
					FROM(
						SELECT
							A.card_type
							,MONTH(A.YMD) AS YMD
							,A.circulate CALMON
						FROM credit_card_count A	
                        WHERE YEAR(A.YMD) = YEAR(GETDATE())
					) A
					GROUP BY A.card_type
)B ON A.card_type = B.card_type
ORDER BY A.CALMON DESC
", getOrderBySQLStatement(),getMAXSQLStatement());
        }

        /// <summary>
        /// 有效卡
        /// </summary>
        /// <returns></returns>
        protected string getEffectiveSQLStatement()
        {
            return string.Format(@"
SELECT 
A.card_type
,ISNULL([1月] ,0)[1月]
,ISNULL([2月] ,0)[2月]
,ISNULL([3月] ,0)[3月]
,ISNULL([4月] ,0)[4月]
,ISNULL([5月] ,0)[5月]
,ISNULL([6月] ,0)[6月]
,ISNULL([7月] ,0)[7月]
,ISNULL([8月] ,0)[8月]
,ISNULL([9月] ,0)[9月]
,ISNULL([10月] ,0)[10月]
,ISNULL([11月] ,0)[11月]
,ISNULL([12月] ,0)[12月]
FROM 
(
{0}
)A LEFT OUTER JOIN (
					SELECT 
                        {1}
					FROM(
						SELECT
							A.card_type
							,MONTH(A.YMD) AS YMD
							,A.effective CALMON
						FROM credit_card_count A	
                        WHERE YEAR(A.YMD) = YEAR(GETDATE())
					) A
					GROUP BY A.card_type
)B ON A.card_type = B.card_type
ORDER BY A.CALMON DESC
", getOrderBySQLStatement(),getMAXSQLStatement());
        }

        /// <summary>
        /// 流通卡比率
        /// </summary>
        /// <returns></returns>
        protected string getCirculatePercentSQLStatement()
        {
            return string.Format(@"
SELECT 
A.card_type
,ISNULL([1月] ,0)[1月]
,ISNULL([2月] ,0)[2月]
,ISNULL([3月] ,0)[3月]
,ISNULL([4月] ,0)[4月]
,ISNULL([5月] ,0)[5月]
,ISNULL([6月] ,0)[6月]
,ISNULL([7月] ,0)[7月]
,ISNULL([8月] ,0)[8月]
,ISNULL([9月] ,0)[9月]
,ISNULL([10月] ,0)[10月]
,ISNULL([11月] ,0)[11月]
,ISNULL([12月] ,0)[12月]
FROM 
(
{0}
)A LEFT OUTER JOIN (
					SELECT 
						{1}
					FROM(
						SELECT
							A.card_type
							,MONTH(A.YMD) AS YMD
							,CASE WHEN A.effective > 0 AND A.circulate > 0 THEN A.circulate / A.effective ELSE 0 END AS CALMON
						FROM credit_card_count A	
                        WHERE YEAR(A.YMD) = YEAR(GETDATE())
					) A
					GROUP BY A.card_type
)B ON A.card_type = B.card_type
ORDER BY A.CALMON DESC
", getOrderBySQLStatement(),getMAXSQLStatement());
        }

        /// <summary>
        /// 以這來做排序時的依據
        /// </summary>
        /// <returns></returns>
        protected string getOrderBySQLStatement()
        {
            return @"
SELECT 
	A.card_type
	,A.CALMON
FROM(
SELECT
		A.card_type
		,MAX(CASE WHEN A.card_type ='專案Pilot' THEN 0 
				  ELSE 
				  CASE WHEN 		
					A.effective > 0 AND A.circulate > 0 THEN A.circulate / A.effective 
					ELSE 0 END					
				END) AS CALMON
	FROM credit_card_count A
	/*WHERE A.ymd = (SELECT MAX(A.ymd)
								FROM credit_card_count A
                    )*/
	GROUP BY A.card_type
	)A";
        }

        protected string getMAXSQLStatement()
        {
            return @"
                        A.card_type 
						,MAX(CASE WHEN A.YMD = 1 THEN A.CALMON ELSE 0 END) [1月]
						,MAX(CASE WHEN A.YMD = 2 THEN A.CALMON ELSE 0 END) [2月]
						,MAX(CASE WHEN A.YMD = 3 THEN A.CALMON ELSE 0 END) [3月]
						,MAX(CASE WHEN A.YMD = 4 THEN A.CALMON ELSE 0 END) [4月]
						,MAX(CASE WHEN A.YMD = 5 THEN A.CALMON ELSE 0 END) [5月]
						,MAX(CASE WHEN A.YMD = 6 THEN A.CALMON ELSE 0 END) [6月]
						,MAX(CASE WHEN A.YMD = 7 THEN A.CALMON ELSE 0 END) [7月]
						,MAX(CASE WHEN A.YMD = 8 THEN A.CALMON ELSE 0 END) [8月]
						,MAX(CASE WHEN A.YMD = 9 THEN A.CALMON ELSE 0 END) [9月]
						,MAX(CASE WHEN A.YMD = 10 THEN A.CALMON ELSE 0 END) [10月]
						,MAX(CASE WHEN A.YMD = 11 THEN A.CALMON ELSE 0 END) [11月]
						,MAX(CASE WHEN A.YMD = 12 THEN A.CALMON ELSE 0 END) [12月]";
        }

        /// <summary>
        /// 取得本期和前一期的差異
        /// </summary>
        /// <returns></returns>
        protected string getThisLastSQLStatement()
        {
            return string.Format( @"
SELECT 
	A.card_type
	,B.[CURRENT]
FROM(
    {0}
	)A LEFT OUTER JOIN (
						SELECT
							A.CARD_TYPE
							,MAX(A.THIS) - MAX(A.LAST) [CURRENT]
						FROM (
								SELECT
									A.card_type
									,CASE WHEN A.YMD = (SELECT MAX(A.ymd)
																FROM credit_card_count A
									)
												THEN CASE WHEN A.effective > 0 AND A.circulate > 0 THEN A.circulate / A.effective ELSE 0 END
												ELSE 0
												END [THIS]
									,CASE WHEN A.YMD <> (SELECT MAX(A.ymd)
																FROM credit_card_count A
									)
												THEN CASE WHEN A.effective > 0 AND A.circulate > 0 THEN A.circulate / A.effective ELSE 0 END
												ELSE 0
												END [LAST]				
								FROM credit_card_count A	
								WHERE A.ymd BETWEEN DATEADD(MONTH,-1,(SELECT MAX(A.ymd)
																	  FROM credit_card_count A)
															) AND (
																SELECT MAX(A.ymd)
																FROM credit_card_count A)                    
						)A GROUP BY A.CARD_TYPE
	)B ON A.CARD_TYPE = B.CARD_TYPE
ORDER BY A.	CALMON DESC",getOrderBySQLStatement());
        }


        protected string getLastYearSQLStatement()
        {
            return string.Format(@"
SELECT 
	A.card_type
	,B.[CURRENT]
FROM(
    {0}
	)A LEFT OUTER JOIN (
						SELECT
							A.CARD_TYPE
							,MAX(A.THIS) - MAX(A.LAST) [current]
						FROM (
								SELECT
									A.card_type
									,CASE WHEN A.YMD = (SELECT MAX(A.ymd)
																FROM credit_card_count A
									)
												THEN CASE WHEN A.effective > 0 AND A.circulate > 0 THEN A.circulate / A.effective ELSE 0 END
												ELSE 0
												END [THIS]
									,CASE WHEN A.YMD <> (SELECT MAX(A.ymd)
																FROM credit_card_count A
									)
												THEN CASE WHEN A.effective > 0 AND A.circulate > 0 THEN A.circulate / A.effective ELSE 0 END
												ELSE 0
												END [LAST]				
								FROM credit_card_count A	
								WHERE A.ymd IN( DATEADD(YEAR,-1,(SELECT MAX(A.ymd)
																	  FROM credit_card_count A)
															) , (
																SELECT MAX(A.ymd)
																FROM credit_card_count A)
												)
						)A GROUP BY A.CARD_TYPE	
						)B ON A.CARD_TYPE = B.CARD_TYPE
ORDER BY A.	CALMON DESC
",getOrderBySQLStatement());
            
        }
    }
}
